package net.hn.hnms.biz.safety.fire.Dao;

import net.hn.hnms.biz.safety.fire.entity.ODSSensorMonitor;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author 智呈物联 nzw
 * @date 2022/8/1
 */
@Repository
public interface ODSSensorMonitorDao extends JpaRepository<ODSSensorMonitor,String> {

    @Query(value = "SELECT\n" +
            "\t* \n" +
            "FROM\n" +
            "\tods_sgjc_his_sensormonitor \n" +
            "WHERE\n" +
            "\t trim(mine_code) = ? \n"+
            "\tand sampling_time BETWEEN ? \n" +
            "\tAND ?",nativeQuery = true)
    List<ODSSensorMonitor> historyByTime(String mineCode,Date state, Date end);

    @Query(value = "SELECT\n" +
            "\t* \n" +
            "FROM\n" +
            "\tods_sgjc_his_sensormonitor \n" +
            "WHERE\n" +
            "\tsampling_location = ? \n" +
            "\tand trim(mine_code) = ? \n"+
            "\tAND sampling_time BETWEEN ? \n" +
            "\tAND ?",nativeQuery = true)
    List<ODSSensorMonitor> historyBySamplingLocation(String samplingLocation,String mineCode,Date state, Date end);

    /**
     * 获取所有地点最新的数据
     * @return
     */
    @Query(value = "SELECT\n" +
            "\tods.* \n" +
            "FROM\n" +
            "\tdws_sgjc_now_sensormonitor ods,\n" +
            "\t( SELECT MAX ( sampling_time ) AS maxTime, sampling_location FROM dws_sgjc_now_sensormonitor GROUP BY sampling_location ) mx \n" +
            "WHERE\n" +
            "\tods.sampling_time = mx.maxTime \n" +
            "\tAND ods.sampling_location = mx.sampling_location\n" +
            "\tand ods.sampling_time > '2022-08-27 00:00:00' " +
            "\tand CASE when ?1 != '' then trim(ods.mine_code) = ?1 else 1=1 end",nativeQuery = true)
    List<ODSSensorMonitor> latestData(String mineCode);

    @Query(value = "SELECT\n" +
            "\t* \n" +
            "FROM\n" +
            "\tdwd_sgjc_now_sensormonitor \n" +
            "WHERE\n" +
            "\tpoint_monitor_location = ?1 " +
            "\tand CASE when ?2 != '' then trim(mine_code) = ?2 else 1=1 end",nativeQuery = true)
    List<Map<String,Object>> latestDataB(String location,String mineCode);

    @Query(value = "SELECT\n" +
            "\t* \n" +
            "FROM\n" +
            "\tdwd_sgjc_now_sensormonitor \n" +
            "WHERE\n" +
            "\tpoint_monitor_location like ?1 " +
            "\tand CASE when ?2 != '' then trim(mine_code) = ?2 else 1=1 end",nativeQuery = true)
    List<Map<String,Object>> latestDataGaoTouYaoB(String location,String mineCode);

    @Query(value = "SELECT\n" +
            "\tpoint_monitor_location \n" +
            "FROM\n" +
            "\tdwd_sgjc_now_sensormonitor \n" +
            "WHERE\n" +
            "CASE\n" +
            "\t\tWHEN ?1 != '' THEN\n" +
            "\t\tTRIM ( mine_code ) = ?1 ELSE 1 = 1 \n" +
            "\tEND \n" +
            "GROUP BY\n" +
            "\tpoint_monitor_location",nativeQuery = true)
    List<Map<String,Object>> findByPointMonitorLocation(String mineCode);

    @Query(value = "select count(1) from dws_sgjc_now_sensormonitor",nativeQuery = true)
    Integer countDwsNow();

    @Query(value = "select count(1) from dwd_sgjc_now_sensormonitor",nativeQuery = true)
    Integer countDwdNow();


    @Query(value = "SELECT\n" +
            "\t* \n" +
            "FROM\n" +
            "\tods_sgjc_his_sensormonitor \n" +
            "WHERE\n" +
            "\tsampling_location = ?1 \n" +
            "\tand CASE when ?2 != '' then trim(mine_code) = ?2 else 1=1 end \n"+
            "\tand sampling_time > ?3 ORDER BY sampling_time",nativeQuery = true)
    List<ODSSensorMonitor> oxygenConcentration(String samplingLocation,String mineCode,Date samplingTime);

    @Query(value = "SELECT\n" +
            "\t* \n" +
            "FROM\n" +
            "\tods_sgjc_his_sensormonitor \n" +
            "WHERE\n" +
            "\tsampling_time BETWEEN ? \n" +
            "\tAND ? \n" +
            "\tORDER BY sampling_time desc\n" +
            "\tLIMIT ? OFFSET ?",nativeQuery = true)
    List<ODSSensorMonitor> pageSize(Date state, Date end,Integer pageSize,Integer pageNum);

    @Query(value = "SELECT\n" +
            "\tcount(1)  \n" +
            "FROM\n" +
            "\tods_sgjc_his_sensormonitor \n" +
            "WHERE\n" +
            "\t CASE when ?1 != '' then trim(mine_code) = ?1 else 1=1 end and "+
            "\tsampling_time BETWEEN ?2 \n" +
            "\tAND ?3\n",nativeQuery = true)
    Integer pageCount(String mineCode,Date state, Date end);

    @Query(value = "SELECT\n" +
            "\tods.* \n" +
            "FROM\n" +
            "\tods_sgjc_his_sensormonitor ods,\n" +
            "\t( SELECT MAX ( sampling_time ) AS maxTime, sampling_location FROM ods_sgjc_his_sensormonitor GROUP BY sampling_location ) mx \n" +
            "WHERE\n" +
            "\tods.sampling_time = mx.maxTime \n" +
            "\tAND ods.sampling_location = ?1\n" +
            "\tAND ods.sampling_location = mx.sampling_location\n" +
            "\tand CASE when ?2 != '' then trim(mine_code) = ?2 else 1=1 end",nativeQuery = true)
    List<ODSSensorMonitor> findByLocation(String samplingLocation,String mineCode);

    @Query(value = "SELECT\n" +
            "\tods.* \n" +
            "FROM\n" +
            "\tods_sgjc_his_sensormonitor ods,\n" +
            "\t( SELECT MAX ( sampling_time ) AS maxTime, sampling_location FROM ods_sgjc_his_sensormonitor where sampling_location like ?1 GROUP BY sampling_location ) mx \n" +
            "WHERE\n" +
            "\tods.sampling_time = mx.maxTime \n" +
            "\tAND ods.sampling_location like ?1\n" +
            "\tAND ods.sampling_location = mx.sampling_location\n" +
            "\tand CASE when ?2 != '' then trim(mine_code) = ?2 else 1=1 end",nativeQuery = true)
    List<ODSSensorMonitor> findByLocationGaoTouYao(String samplingLocation,String mineCode);

    @Query(value = "SELECT COUNT\n" +
            "\t( * ) \n" +
            "FROM\n" +
            "\tdws_sgjc_now_sensormonitor \n" +
            "WHERE\n" +
            "\t\"state\" != 'No'" +
            "\tand CASE when ?1 != '' then trim(mine_code) = ?1 else 1=1 end",nativeQuery = true)
    Long alarms(String mineCode);

    @Query(value = "SELECT\n" +
            "\tto_char( sampling_time, 'yyyy-MM-dd' ) dataTime,\n" +
            "\t\"count\" ( * ) \n" +
            "FROM\n" +
            "\tdws_sgjc_now_sensormonitor \n" +
            "WHERE\n" +
            "\t\"state\" != 'No' \n" +
            "\tAND to_char(sampling_time,'yyyy-MM-dd') >= ?1 \n" +
            "\tAND to_char(sampling_time,'yyyy-MM-dd') <= ?2 \n" +
            "GROUP BY\n" +
            "\tdataTime \n" +
            "ORDER BY\n" +
            "\tdataTime",nativeQuery = true)
    List<Map<String,String>> findAllAlarm(String start,String end);

    @Query(value = "SELECT\n" +
            "\t* \n" +
            "FROM\n" +
            "\tdwd_sgjc_now_sensormonitor \n" +
            "WHERE\n" +
            "\tto_char(data_time,'yyyy-MM-dd') > ? \n" +
            "\tAND point_status != 0 \n" +
            "ORDER BY\n" +
            "\tdata_time",nativeQuery = true)
    List<Map<String,Object>> findAlarms(String start);

    @Query(value = "SELECT\n" +
            "\tsampling_location \n" +
            "FROM\n" +
            "\tods_sgjc_his_sensormonitor \n" +
            "WHERE\n" +
            "\tsampling_location LIKE'%采空区%' \n" +
            "\tAND mine_code = ?\n" +
            "GROUP BY\n" +
            "\tsampling_location",nativeQuery = true)
    List<Map<String,Object>> findAddress(String mineCode);
}
